package persistencia;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.Conexion;
import bean.Resultado;
import bean.Veritrade;

public class VeritradeDAO {
	
	public int insertar(Veritrade veritrade) throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexionMSQL().prepareStatement(
				"insert into tbl_Veritrade_work (ejercicio,periodo,tipo_informacion,HTS_Code,HTS_Code_Description,Custom,DUA,Date,ETA,BL_No,Tax_ID,Importer,Exporter," +
				"Gross_kg,Net_kg,Qty_1,Unit_1,Qty_2,Unit_2,U$_FOB_Tot,U$_Freight_Tot,U$_CFR_Tot,U$_Insurance_Tot,U$_CIF_Tot,U$_FOB_Unit_1,U$_CFR_Unit_1,U$_CIF_Unit_1,"+
				"U$_CIF__Taxes_Unit_1,U$_FOB_Unit_2,U$_CFR_Unit_2,U$_CIF_Unit_2,U$_CIF__Taxes_Unit_2,Origin_Country,Loading_Country,Loading_Port,Via,Transport,Custom1, "+
				"Bank,Warehouse,Status,Commercial_Description,usuario_creacion,fecha_creacion) " +
				"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
		pstm.setInt(1, veritrade.getEjercicio());
		pstm.setString(2, veritrade.getPeriodo());
		pstm.setString(3, veritrade.getTipo_informacion());
		pstm.setString(4, veritrade.getHTS_Code());
		pstm.setString(5, veritrade.getHTS_Code_Description());
		pstm.setString(6, veritrade.getCustom());
		pstm.setString(7, veritrade.getDUA());
		pstm.setString(8, veritrade.getDate());
		pstm.setString(9, veritrade.getETA());
		pstm.setString(10, veritrade.getBL_No());
		pstm.setString(11, veritrade.getTax_ID());
		pstm.setString(12, veritrade.getImporter());
		pstm.setString(13, veritrade.getExporter());
		pstm.setDouble(14, veritrade.getGross_kg());
		pstm.setDouble(15, veritrade.getNet_kg());
		pstm.setDouble(16, veritrade.getQty_1());
		pstm.setString(17, veritrade.getUnit_1());
		pstm.setDouble(18, veritrade.getQty_2());
		pstm.setString(19, veritrade.getUnit_2());
		pstm.setDouble(20, veritrade.getU$_FOB_Tot());
		pstm.setDouble(21, veritrade.getU$_Freight_Tot());
		pstm.setDouble(22, veritrade.getU$_CFR_Tot());
		pstm.setDouble(23, veritrade.getU$_Insurance_Tot());
		pstm.setDouble(24, veritrade.getU$_CIF_Tot());
		pstm.setDouble(25, veritrade.getU$_FOB_Unit_1());
		pstm.setDouble(26, veritrade.getU$_CFR_Unit_1());
		pstm.setDouble(27, veritrade.getU$_CIF_Unit_1());
		pstm.setDouble(28, veritrade.getU$_CIF__Taxes_Unit_1());
		pstm.setDouble(29, veritrade.getU$_FOB_Unit_2());
		pstm.setDouble(30, veritrade.getU$_CFR_Unit_2());
		pstm.setDouble(31, veritrade.getU$_CIF_Unit_2());
		pstm.setDouble(32, veritrade.getU$_CIF__Taxes_Unit_2());
		pstm.setString(33, veritrade.getOrigin_Country());
		pstm.setString(34, veritrade.getLoading_Country());
		pstm.setString(35, veritrade.getLoading_Port());
		pstm.setString(36, veritrade.getVia());
		pstm.setString(37, veritrade.getTransport());
		pstm.setString(38, veritrade.getCustom1());
		pstm.setString(39, veritrade.getBank());
		pstm.setString(40, veritrade.getWarehouse());
		pstm.setString(41, veritrade.getStatus());
		pstm.setString(42, veritrade.getCommercial_Description());
		pstm.setString(43, veritrade.getUsuario_creacion());
		pstm.setInt(44,veritrade.getFecha_creacion());
		int resultado = pstm.executeUpdate();
		pstm.close();
		return resultado;
	}
	
	public List<Veritrade> obtenerVeritrade(Veritrade veritrade) throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexionMSQL().prepareStatement(
				"select id,marca,medida,modelo,descripcion_art,cilindrada,color,cod_tipo,tipo,vin_fabricante,vin,estilo,DUA,BL_No,Commercial_Description from tbl_Veritrade_work where ejercicio=? and periodo=? and tipo_informacion=?");
		pstm.setInt(1, veritrade.getEjercicio());
		pstm.setString(2, veritrade.getPeriodo());
		pstm.setString(3, veritrade.getTipo());
		ResultSet rs = pstm.executeQuery();
		List<Veritrade> listado = new ArrayList<Veritrade>();
		while(rs.next()){
			Veritrade obj = new Veritrade();
			obj.setId(rs.getInt("id"));
			obj.setMarca(rs.getString("marca"));
			obj.setMedida(rs.getString("medida"));
			obj.setModelo(rs.getString("modelo"));
			obj.setDescripcion_art(rs.getString("descripcion_art"));
			obj.setCilindrada(rs.getString("cilindrada"));
			obj.setColor(rs.getString("color"));
			obj.setCod_Tipo(rs.getString("cod_tipo"));
			obj.setTipo(rs.getString("tipo"));
			obj.setVin_fabricante(rs.getString("vin_fabricante"));
			obj.setVin(rs.getString("vin"));
			obj.setEstilo(rs.getString("estilo"));
			obj.setDUA(rs.getString("DUA"));
			obj.setBL_No(rs.getString("BL_No"));
			obj.setCommercial_Description(rs.getString("Commercial_Description"));
			listado.add(obj);
		}
		rs.close();
		pstm.close();
		return listado;
	}
	
	public List<Veritrade> obtenerVeritradePorFlagMarca(Veritrade veritrade) throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexionMSQL().prepareStatement(
				"select id,modelo,Commercial_Description from tbl_Veritrade_work where ejercicio=? and periodo=? and tipo_informacion=? and flag_modelo=?");
		pstm.setInt(1, veritrade.getEjercicio());
		pstm.setString(2, veritrade.getPeriodo());
		pstm.setString(3, veritrade.getTipo());
		pstm.setInt(4, veritrade.getFlag_modelo());
		ResultSet rs = pstm.executeQuery();
		List<Veritrade> listado = new ArrayList<Veritrade>();
		while(rs.next()){
			Veritrade obj = new Veritrade();
			obj.setId(rs.getInt("id"));
			obj.setModelo(rs.getString("modelo"));
			obj.setCommercial_Description(rs.getString("Commercial_Description"));
			listado.add(obj);
		}
		rs.close();
		pstm.close();
		return listado;
	}
	
	public Resultado completar(String sp,Veritrade veritrade) throws SQLException{
		System.out.println(" in call " + sp);
		//Connection cn = Conexion.obtenerMSQL();
		Conexion.obtenerMSQL().setAutoCommit(false);
		Conexion.obtenerMSQL().setTransactionIsolation(Connection.TRANSACTION_NONE);
		CallableStatement cst = Conexion.obtenerConexionMSQL().prepareCall("{call "+sp+" (?,?,?,?,?,?,?)}");
		cst.setInt(1,veritrade.getEjercicio());
		cst.setString(2,veritrade.getPeriodo());
		cst.setString(3,veritrade.getTipo());
		cst.setString(4,veritrade.getUsuario_modificacion());
		cst.setInt(5,veritrade.getFecha_modificacion());
		cst.registerOutParameter(6, java.sql.Types.INTEGER);
		cst.registerOutParameter(7, java.sql.Types.INTEGER);
		cst.executeUpdate();
		System.out.println(" out call " +sp);
		Resultado r = new Resultado();
		r.setAntes(cst.getInt(6));
		r.setDespues(cst.getInt(7));
		System.out.println("parametros "+sp);
		cst.close();
		System.out.println("close "+sp);
		return r;
	}
	
	public int migrarWorkToVeritrade(String sp,Veritrade veritrade) throws SQLException{
		System.out.println(" in call " + sp);
		CallableStatement cst = Conexion.obtenerConexionMSQL().prepareCall("{call "+sp+" (?,?,?)}");
		cst.setInt(1,veritrade.getEjercicio());
		cst.setString(2,veritrade.getPeriodo());
		cst.setString(3,veritrade.getTipo());
		int resultado = cst.executeUpdate();
		System.out.println(" out call " +sp);
		cst.close();
		System.out.println("close "+sp);
		return resultado;
	}
	
	public int actualizaPorFlagMarca(Veritrade veritrade) throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexionMSQL().prepareStatement(
				"update tbl_Veritrade_work set modelo=?,flag_modelo=? where id=?");
		pstm.setString(1, veritrade.getModelo());
		pstm.setInt(2, veritrade.getFlag_modelo());
		pstm.setInt(3, veritrade.getId());
		int resultado = pstm.executeUpdate();
		pstm.close();
		return resultado;
	}
	
	public List<Veritrade> obtenerVeritrade(String columna,String valor) throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexionMSQL().prepareStatement(
				"select id,"+columna+",Commercial_Description from tbl_Veritrade_work where "+columna+"=? ");
		pstm.setString(1, valor);
		ResultSet rs = pstm.executeQuery();
		List<Veritrade> listado = new ArrayList<Veritrade>();
		while(rs.next()){
			Veritrade obj = new Veritrade();
			obj.setId(rs.getInt("id"));
			obj.setGenerico(rs.getString(columna));
			obj.setCommercial_Description(rs.getString("Commercial_Description"));
			listado.add(obj);
		}
		rs.close();
		pstm.close();
		return listado;
	}
	
	public int actualizaColumna(String columna,String valor,int id) throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexionMSQL().prepareStatement(
				"update tbl_Veritrade_work set "+columna+"=? where id=?");
		pstm.setString(1, valor);
		pstm.setInt(2, id);
		int resultado = pstm.executeUpdate();
		pstm.close();
		return resultado;
	}
	
	public int actualizaColumna(String columna,String valor) throws SQLException{
		PreparedStatement pstm = Conexion.obtenerConexionMSQL().prepareStatement(
				"update tbl_Veritrade_work set "+columna+"=? where Commercial_Description like ?");
		pstm.setString(1, valor);
		pstm.setString(2, "%"+valor+"%");
		int resultado = pstm.executeUpdate();
		pstm.close();
		return resultado;
	}
	
}
